home *** CD-ROM | disk | FTP | other *** search
/ Winzipper / Winzipper_ISO.iso / programming / oracle7 7.2 / DB / UTIL72 / CATBLOCK.SQL < prev    next >
Encoding:
Text File  |  1995-05-09  |  8.8 KB  |  265 lines
  1. rem 
  2. rem $Header: catblock.sql 7020100.1 94/09/23 22:14:32 cli Generic<base> $ blocking.sql 
  3. rem 
  4. Rem Copyright (c) 1989 by Oracle Corporation
  5. Rem NAME
  6. Rem    catblock.sql
  7. Rem  FUNCTION  -  create views of oracle locks
  8. Rem  NOTES
  9. Rem  MODIFIED
  10. Rem     drady      03/22/93 -  merge changes from branch 1.1.312.1 
  11. Rem     drady      03/18/93 -  fix 154271 
  12. Rem     glumpkin   10/17/92 -  renamed from BLOCKING.SQL 
  13. Rem     tpystyne   09/14/92 -  rename sid to session_id 
  14. Rem     jloaiza    07/30/92 -  fix for KGL change 
  15. Rem   tpystyne   05/27/92 - add dba_dml_locks and dba_ddl_locks views
  16. Rem   jloaiza    05/24/91 - upgrade for v7 
  17. Rem   Loaiza     11/01/89 - Creation
  18. Rem
  19.  
  20.  
  21. /* this is an auxiliary view containing the KGL locks and pins */
  22. drop view DBA_KGLLOCK;
  23. create view DBA_KGLLOCK as
  24.   select kgllkuse, kgllkhdl, kgllkmod, kgllkreq, 'Lock' kgllktype from x$kgllk
  25.  union all
  26.   select kglpnuse, kglpnhdl, kglpnmod, kglpnreq, 'Pin'  kgllktype from x$kglpn;
  27.  
  28. /* 
  29.  * DBA_LOCK has a row for each lock that is being held, and 
  30.  * one row for each outstanding request for a lock or latch.
  31.  * The columns of DBA_LOCK are:
  32.  *   session_id     - session holding or acquiring the lock
  33.  *   type           - type of lock
  34.  *   mode_held      - mode the lock is currently held in by the session
  35.  *   mode_requested - mode that the lock is being requested in by the process
  36.  *   lock_id1            - type specific identifier of the lock
  37.  *   lock_id2            - type specific identifier of the lock
  38.  */
  39. drop synonym DBA_LOCKS;
  40. drop view DBA_LOCKS;
  41. drop view DBA_LOCK;
  42. create view DBA_LOCK as
  43.   select 
  44.     sid session_id,
  45.     decode(type, 
  46.         'MR', 'Media Recovery', 
  47.         'RT', 'Redo Thread',
  48.         'UN', 'User Name',
  49.         'TX', 'Transaction',
  50.         'TM', 'DML',
  51.         'UL', 'PL/SQL User Lock',
  52.         'DX', 'Distributed Xaction',
  53.         'CF', 'Control File',
  54.         'IS', 'Instance State',
  55.         'FS', 'File Set',
  56.         'IR', 'Instance Recovery',
  57.         'ST', 'Disk Space Transaction',
  58.         'TS', 'Temp Segment',
  59.         'IV', 'Library Cache Invalidation',
  60.         'LS', 'Log Start or Switch',
  61.         'RW', 'Row Wait',
  62.         'SQ', 'Sequence Number',
  63.         'TE', 'Extend Table',
  64.         'TT', 'Temp Table',
  65.         type) lock_type,
  66.     decode(lmode, 
  67.         0, 'None',           /* Mon Lock equivalent */
  68.         1, 'Null',           /* N */
  69.         2, 'Row-S (SS)',     /* L */
  70.         3, 'Row-X (SX)',     /* R */
  71.         4, 'Share',          /* S */
  72.         5, 'S/Row-X (SSX)',  /* C */
  73.         6, 'Exclusive',      /* X */
  74.         to_char(lmode)) mode_held,
  75.          decode(request,
  76.         0, 'None',           /* Mon Lock equivalent */
  77.         1, 'Null',           /* N */
  78.         2, 'Row-S (SS)',     /* L */
  79.         3, 'Row-X (SX)',     /* R */
  80.         4, 'Share',          /* S */
  81.         5, 'S/Row-X (SSX)',  /* C */
  82.         6, 'Exclusive',      /* X */
  83.         to_char(request)) mode_requested,
  84.          to_char(id1) lock_id1, to_char(id2) lock_id2
  85.       from v$lock;               /* processes waiting on or holding enqueues */
  86. create synonym DBA_LOCKS for DBA_LOCK;
  87.  
  88. /*
  89.  * DBA_LOCK_INTERNAL has a row for each lock or latch that is being held, and 
  90.  * one row for each outstanding request for a lock or latch.
  91.  * The columns  of DBA_LOCK_INTERNAL are:
  92.  *   session_id     - session holding or acquiring the lock
  93.  *   type           - type of lock (DDL, LATCH, etc.)
  94.  *   mode_held      - mode the lock is currently held in by the session
  95.  *   mode_requested - mode that the lock is being requested in by the process
  96.  *   lock_id1            - type specific identifier of the lock
  97.  *   lock_id2            - type specific identifier of the lock
  98.  *
  99.  * NOTE: this view can be very, very slow depending on the size of your
  100.  *     shared pool area and database activity.
  101.  */
  102. drop view DBA_LOCK_INTERNAL;
  103. create view DBA_LOCK_INTERNAL as
  104.   select 
  105.     sid session_id,
  106.     decode(type, 
  107.         'MR', 'Media Recovery', 
  108.         'RT', 'Redo Thread',
  109.         'UN', 'User Name',
  110.         'TX', 'Transaction',
  111.         'TM', 'DML',
  112.         'UL', 'PL/SQL User Lock',
  113.         'DX', 'Distributed Xaction',
  114.         'CF', 'Control File',
  115.         'IS', 'Instance State',
  116.         'FS', 'File Set',
  117.         'IR', 'Instance Recovery',
  118.         'ST', 'Disk Space Transaction',
  119.         'TS', 'Temp Segment',
  120.         'IV', 'Library Cache Invalidation',
  121.         'LS', 'Log Start or Switch',
  122.         'RW', 'Row Wait',
  123.         'SQ', 'Sequence Number',
  124.         'TE', 'Extend Table',
  125.         'TT', 'Temp Table',
  126.         type) lock_type,
  127.     decode(lmode, 
  128.         0, 'None',           /* Mon Lock equivalent */
  129.         1, 'Null',           /* N */
  130.         2, 'Row-S (SS)',     /* L */
  131.         3, 'Row-X (SX)',     /* R */
  132.         4, 'Share',          /* S */
  133.         5, 'S/Row-X (SSX)',  /* C */
  134.         6, 'Exclusive',      /* X */
  135.         to_char(lmode)) mode_held,
  136.          decode(request,
  137.         0, 'None',           /* Mon Lock equivalent */
  138.         1, 'Null',           /* N */
  139.         2, 'Row-S (SS)',     /* L */
  140.         3, 'Row-X (SX)',     /* R */
  141.         4, 'Share',          /* S */
  142.         5, 'S/Row-X (SSX)',  /* C */
  143.         6, 'Exclusive',      /* X */
  144.         to_char(request)) mode_requested,
  145.          to_char(id1) lock_id1, to_char(id2) lock_id2
  146.       from v$lock                /* processes waiting on or holding enqueues */
  147.  union all                                          /* procs holding latches */
  148.   select sid, 'LATCH', 'Exclusive', 'None', rawtohex(laddr), ' '
  149.     from v$process p, v$session s, v$latchholder h
  150.    where h.pid  = p.pid                       /* 6 = exclusive, 0 = not held */
  151.     and  p.addr = s.paddr
  152.  union all                                         /* procs waiting on latch */
  153.   select sid, 'LATCH', 'None', 'Exclusive', latchwait,' '
  154.      from v$session s, v$process p
  155.     where latchwait is not null
  156.      and  p.addr = s.paddr
  157.  union all                                            /* library cache locks */
  158.   select  s.sid,
  159.     decode(ob.kglhdnsp, 0, 'Cursor', 1, 'Table/Procedure', 2, 'Body', 
  160.          3, 'trigger', 4, 'Index', 5, 'Cluster', to_char(ob.kglhdnsp))
  161.       || ' Definition ' || lk.kgllktype,
  162.     decode(lk.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
  163.        to_char(lk.kgllkmod)),
  164.     decode(lk.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
  165.        to_char(lk.kgllkreq)),
  166.     decode(ob.kglnaown, null, '', ob.kglnaown || '.') || ob.kglnaobj ||
  167.     decode(ob.kglnadlk, null, '', '@' || ob.kglnadlk),
  168.     rawtohex(lk.kgllkhdl)
  169.    from v$session s, x$kglob ob, dba_kgllock lk
  170.      where lk.kgllkhdl = ob.kglhdadr
  171.       and  lk.kgllkuse = s.saddr;
  172.   
  173. /*
  174.  * DBA_DML_LOCKS has a row for each DML lock that is being held, and 
  175.  * one row for each outstanding request for a DML lock. It is subset
  176.  * of DBA_LOCKS
  177.  */
  178.  
  179. drop view DBA_DML_LOCKS;
  180.  
  181. create view DBA_DML_LOCKS as
  182.   select 
  183.     sid session_id,
  184.         u.name owner,
  185.         o.name,
  186.     decode(lmode, 
  187.         0, 'None',           /* Mon Lock equivalent */
  188.         1, 'Null',           /* N */
  189.         2, 'Row-S (SS)',     /* L */
  190.         3, 'Row-X (SX)',     /* R */
  191.         4, 'Share',          /* S */
  192.         5, 'S/Row-X (SSX)',  /* C */
  193.         6, 'Exclusive',      /* X */
  194.         'Invalid') mode_held,
  195.          decode(request,
  196.         0, 'None',           /* Mon Lock equivalent */
  197.         1, 'Null',           /* N */
  198.         2, 'Row-S (SS)',     /* L */
  199.         3, 'Row-X (SX)',     /* R */
  200.         4, 'Share',          /* S */
  201.         5, 'S/Row-X (SSX)',  /* C */
  202.         6, 'Exclusive',      /* X */
  203.         'Invalid') mode_requested
  204.       from v$lock l, obj$ o, user$ u
  205.       where l.id1 = o.obj#
  206.       and   o.owner# = u.user#
  207.       and   l.type = 'TM';
  208.  
  209. /*
  210.  * DBA_DDL_LOCKS has a row for each DDL lock that is being held, and 
  211.  * one row for each outstanding request for a DDL lock. It is subset
  212.  * of DBA_LOCKS
  213.  */
  214.  
  215. drop view DBA_DDL_LOCKS;
  216.  
  217. create view DBA_DDL_LOCKS as
  218.   select  s.sid session_id,
  219.           substr(ob.kglnaown,1,30) owner,
  220.           substr(ob.kglnaobj,1,30) name,
  221.     decode(ob.kglhdnsp, 0, 'Cursor', 1, 'Table/Procedure', 2, 'Body', 
  222.          3, 'Trigger', 4, 'Index', 5, 'Cluster', 'Unknown') type,
  223.     decode(lk.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
  224.        'Unknown') mode_held,
  225.     decode(lk.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
  226.        'Unknown') mode_requested
  227.    from v$session s, x$kglob ob, x$kgllk lk
  228.    where lk.kgllkhdl = ob.kglhdadr
  229.    and   lk.kgllkuse = s.saddr
  230.    and   ob.kglhdnsp != 0;
  231.  
  232. /*
  233.  * Show all the sessions waiting for locks and the session that holds the 
  234.  * lock.
  235.  */
  236. drop view DBA_WAITERS;
  237.  
  238. create view DBA_WAITERS as
  239.  select w.session_id  waiting_session,
  240.     h.session_id  holding_session,
  241.     w.lock_type, 
  242.     h.mode_held, 
  243.     w.mode_requested,
  244.     w.lock_id1,
  245.     w.lock_id2
  246.   from dba_locks w, dba_locks h
  247.  where h.mode_held    !=  'None'
  248.   and  h.mode_held    !=  'Null'
  249.   and  w.mode_requested !=  'None'
  250.   and  w.lock_type     =  h.lock_type
  251.   and  w.lock_id1     =  h.lock_id1
  252.   and  w.lock_id2        =  h.lock_id2;
  253.  
  254. /*
  255.  * Show all the sessions that have someone waiting on a lock they hold, but
  256.  * that are not themselves waiting on a lock.
  257.  */
  258. drop view DBA_BLOCKERS;
  259.  
  260. create view DBA_BLOCKERS as
  261.  select holding_session from dba_waiters
  262. minus
  263.  select session_id from dba_locks w
  264.    where w.mode_requested != 'None';
  265.